package br.com.portalecbahia.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import br.com.portalecbahia.model.Action;
import br.com.portalecbahia.model.Match;
import br.com.portalecbahia.model.TypeAction;
import br.com.portalecbahia.util.PortalECBahiaUtil;

public class ActionDAO {

	private Context context;
	private SQLiteDatabase database;
	private DBHelper dbHelper;

	public ActionDAO(Context context) {
		this.context = context;
	}

	public ActionDAO openWritable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getWritableDatabase();
		return this;
	}
	public ActionDAO openReadable() throws SQLException {
		dbHelper = new DBHelper(context);
		database = dbHelper.getReadableDatabase();
		return this;
	}
	

	public void close() {
		dbHelper.close();
	}
	
	
	public void insertByMatch(JSONArray jsonArray, Match match) throws JSONException, ParseException {
		this.openWritable();
		Cursor cursor = null;
		try{
			database.beginTransaction();
			for (int i = 0; i < jsonArray.length(); i++) {
				JSONObject json = jsonArray.getJSONObject(i);
				String team = null;
				try{
					team = json.getString("team");
				}catch (Exception e) {
					// TODO: handle exception
				}
				cursor = database.rawQuery(" INSERT INTO tbl_actions (ID,  idMatch, description, minute, type, team)  VALUES (" +
								"" +json.getLong("id")           +",  " +
				         		"" +match.getId()		 	     +",  "+
				         		"'"+json.getString("description")+"', " +
								"'"+json.getString("minutes") 	 +"', "+
								""+json.getInt("type") 	 +", " +
								"'"+team+"');", null);
					
					cursor.moveToFirst();
					cursor.close();
				
					
				
			}
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}finally {
			
			database.setTransactionSuccessful();
			database.endTransaction();
			
		}
		
		this.close();
		
	}
	
	public List<Action> findByMatch (Match match) {
		this.openReadable();

		List<Action> result = new ArrayList<Action>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "idMatch, " +
									   "description, " +
									   "minute, " +
									   "type, "+
									   "team " +
									   "FROM tbl_actions WHERE idMatch ="+match.getId() +" "+
									   "order by ID desc", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	public List<Action> findMainByMatch (Match match) {
		this.openReadable();

		List<Action> result = new ArrayList<Action>();		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT ID," +
									   "idMatch, " +
									   "description, " +
									   "minute, " +
									   "type, "+
									   "team " +
									   "FROM tbl_actions WHERE idMatch ="+match.getId() +" and (type<=4)  "+
									   "order by ID desc", null);
			cursor.moveToFirst();
			
			if (!cursor.isAfterLast()){
				do{
					result.add(this.bindSQLite(cursor));
				}while (cursor.moveToNext());
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	
	public String findLastUpdate (Match match) {
		this.openReadable();

		String result = "";		
		Cursor cursor;
			 
		try{
			cursor = database.rawQuery("SELECT date " +
									   "FROM tbl_last_update_action " +
									   "WHERE idMatch = "+match.getId(), null);
			cursor.moveToFirst();
//			SimpleDateFormat dateFormat1 = new  SimpleDateFormat("yyyy-MM-dd HH:mm");
//			SimpleDateFormat dateFormat2 = new  SimpleDateFormat("");

			if (!cursor.isAfterLast()){
//					try {
//						 Date date = dateFormat1.parse(cursor.getString(0));
						result =  cursor.getString(0);
//					} catch (ParseException e) {
//						// TODO Auto-generated catch block
//						e.printStackTrace();
//					}
			
			}
			
			cursor.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		this.close();
		
		return result;
	}
	
	public void updateLastUpdateAction(Match match){
		String lastUpdate = this.findLastUpdate(match);
		this.openWritable();
		Cursor cursor = null;
		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy HH:mm");
		String currentDateandTime = sdf.format(new Date());
		try{
			if(!"".equals(lastUpdate)){
				cursor = database.rawQuery("UPDATE  tbl_last_update_action " +
										   "SET date ='" +currentDateandTime+"'"+
										   "WHERE idMatch ="+match.getId(), null);
				cursor.moveToFirst();
				cursor.close();
			}else{
				cursor = database.rawQuery(" INSERT INTO tbl_last_update_action (date,  idMatch)  VALUES (" +
						"'"+currentDateandTime+"' ,  "+match.getId() +");", null);
				cursor.moveToFirst();
				cursor.close();

			}
			this.close();
		}catch (SQLException e){
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		
		
		
	}
	
	
	
	
	public void removeByMatch(Match match) {
		this.openWritable();
		Cursor cursor = null;
		try{
			cursor = database.rawQuery("DELETE FROM tbl_actions WHERE idMatch="+match.getId(), null);
			cursor.moveToFirst();
			cursor.close();
		}catch (SQLException e) {
			Log.e(PortalECBahiaUtil.TAG, e.toString());
		}
		this.close();
		
	}
	
	private Action bindSQLite(Cursor cursor){
		Action action  = new Action();
		action.setMatch(new Match());
		action.setType(new TypeAction());
		for (int i = 0; i < cursor.getColumnCount(); i++){
			
			if("ID".equals(cursor.getColumnName(i)))
				action.setId(cursor.getLong(i));
			else if("idMatch".equals(cursor.getColumnName(i)))
				action.getMatch().setId(cursor.getLong(i));
			else if("description".equals(cursor.getColumnName(i))) 
				action.setDescription(cursor.getString(i));
			else if("minute".equals(cursor.getColumnName(i))) 
				action.setMinutes(cursor.getString(i));
			else if("type".equals(cursor.getColumnName(i))) 
				action.getType().setId(cursor.getLong(i));
			else if("team".equals(cursor.getColumnName(i))) 
				action.setTeam(cursor.getString(i));
		}
		return action;
	}


}
